package shanpao.imohoo.com.testdb;

import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

import java.util.ArrayList;
import java.util.List;

/**
 * Created by zhaobo on 2016/8/11.
 */
public class SpDBHelper {
    private static SpDBHelper instance;
    public static final String SP_TABLE_NAME = "sp_point";

    public static SpDBHelper getInstance() {
        if (instance == null) {
            synchronized (SpDBHelper.class) {
                if (instance == null) {
                    instance = new SpDBHelper();
                }
            }
        }
        return instance;
    }

    public void insert(SpPoint spPoint) {
        SQLiteDatabase db = CacheDBHelper.getInstance().openDatabase();
        try {
            db.beginTransaction();

            db.execSQL("insert into " + SP_TABLE_NAME + " (user_id,area_id,click_time,extra,status) values(?,?,?,?,?)",
                    new Object[]{
                            spPoint.getUser_id(),
                            spPoint.getArea_id(),
                            spPoint.getClick_time(),
                            spPoint.getExtra(),
                            0
                    });
            Cursor cursor = db.rawQuery("select last_insert_rowid() rowid;", new String[]{});
            cursor.moveToFirst();
            spPoint.setId(cursor.getInt(0));
            cursor.close();
            db.setTransactionSuccessful();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                db.endTransaction();
                CacheDBHelper.getInstance().closeDatabase();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }

    public void update(List<SpPoint> spPoints) {
        SQLiteDatabase db = CacheDBHelper.getInstance().openDatabase();
        try {
            db.beginTransaction();

            for (SpPoint spPoint : spPoints) {
                db.execSQL("update " + SP_TABLE_NAME + " set status=? where id=?",
                        new Object[]{
                                -1,
                                spPoint.getId()
                        });
            }
            db.setTransactionSuccessful();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                db.endTransaction();
                CacheDBHelper.getInstance().closeDatabase();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }

    public void clear() {
        SQLiteDatabase db = CacheDBHelper.getInstance().openDatabase();
        try {
            db.execSQL("delete from " + SP_TABLE_NAME + " where status=-1");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            CacheDBHelper.getInstance().closeDatabase();
        }
    }

    public List<SpPoint> getSp() {
        List<SpPoint> list = new ArrayList<>();
        SQLiteDatabase db = CacheDBHelper.getInstance().openDatabase();
        try {
            Cursor cursor = db.rawQuery("select * from " + SP_TABLE_NAME + " where status=0", null);
            if (cursor == null)
                return list;
            if (cursor.getCount() > 0) {
                cursor.moveToFirst();
                do {
                    SpPoint spPoint = new SpPoint();
                    spPoint.setId(cursor.getInt(cursor.getColumnIndex("id")));
                    spPoint.setUser_id(cursor.getInt(cursor.getColumnIndex("user_id")));
                    spPoint.setArea_id(cursor.getInt(cursor.getColumnIndex("area_id")));
                    spPoint.setExtra(cursor.getString(cursor.getColumnIndex("extra")));
                    spPoint.setClick_time(cursor.getInt(cursor.getColumnIndex("click_time")));
                    spPoint.setStatus(cursor.getInt(cursor.getColumnIndex("status")));

                    list.add(spPoint);
                } while (cursor.moveToNext());
            }
            // 关闭游标
            cursor.close();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            CacheDBHelper.getInstance().closeDatabase();
        }
        return list;
    }
}
